package org.farec.tcc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.farec.tcc.modelo.Area;

public class AreaDAO {
	
	private Connection connection;
		
	public AreaDAO(Connection connection)
	{
		this.connection=connection;
	}
	
	public void adiciona(Area area)
	{
		try
		{
			PreparedStatement stmt = null;
			
			stmt = connection.prepareStatement("SELECT id FROM responsavel WHERE nome=? and idempresa=?");	
			stmt.setString(1, area.getResponsavel());
			stmt.setInt(2, area.getCodEmpresa());
			
			ResultSet rs = stmt.executeQuery();
			
			if(rs.next())
			{
				area.setCodResponsavel(rs.getInt("id"));
				
				//Prepara o statement para insercao
				String sql = "INSERT INTO area(nome, idresponsavel, idempresa) VALUES(?,?,?)";
				stmt = connection.prepareStatement(sql);
				
				//seta os dados
				stmt.setString(1, area.getNome());
				stmt.setInt(2, area.getCodResponsavel());
				stmt.setInt(3, area.getCodEmpresa());
							
				stmt.execute();
				stmt.close();
			}

		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
	}

	public List<Area> getLista(String ordem)
	{
		List<Area> areas = new ArrayList<Area>();
		
		return areas;
	}
	
	public List<Area> getLista(int codEmpresa, String ordem)
	{
		try
		{
			String sql ="SELECT a.id, a.nome, res.nome as responsavel from area as a, responsavel as res WHERE a.idempresa=? and a.idresponsavel=res.id order by "+ordem;
			PreparedStatement stmt = this.connection.prepareStatement(sql);
		
			stmt.setInt(1, codEmpresa);
			
			ResultSet rs = stmt.executeQuery();
			List<Area> areas = new ArrayList<Area>();
			
			while(rs.next())
			{
				//Criando um nova instancia
				Area area = new Area();
				area.setCodigo(rs.getInt("id"));
				area.setNome(rs.getString("nome"));
				area.setResponsavel(rs.getString("responsavel"));
								
				//Adicionando objeto a lista
				areas.add(area);
			}
			
			rs.close();
			stmt.close();
			return areas;
			
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		
	}
	
	public void altera(Area area)
	{
		try
		{	
			PreparedStatement stmt = null;
			
			stmt = connection.prepareStatement("SELECT id FROM responsavel WHERE nome=? and idempresa=?");
			stmt.setString(1, area.getResponsavel());
			stmt.setInt(2, area.getCodEmpresa());
			
			ResultSet rs = stmt.executeQuery();
			
			if(rs.next())
			{
				String sql = "UPDATE area SET nome=?, idresponsavel=? where id=? and idempresa=?";
				
				stmt = this.connection.prepareStatement(sql);
				stmt.setString(1, area.getNome());
				stmt.setInt(2, rs.getInt("id"));
				stmt.setInt(3, area.getCodigo());
				stmt.setInt(4, area.getCodEmpresa());
				
				stmt.execute();
				stmt.close();
			}
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
	}
	
	public Area remove(Area area)
	{
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement("SELECT usuario from usuario where idarea=? and idempresa=?");
			stmt.setInt(1, area.getCodigo());
			stmt.setInt(2, area.getCodEmpresa());
			
			ResultSet rs = stmt.executeQuery();
			
			if(!rs.next())
			{
				PreparedStatement stmt2 = this.connection.prepareStatement("DELETE from area where id=? and idempresa=?");
				stmt2.setLong(1, area.getCodigo());
				stmt2.setLong(2, area.getCodEmpresa());
				
				stmt2.execute();
				stmt2.close();
				
				area.setAlerta(false);
			}
			
			else
			{
				area.setAlerta(true);
			}			
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		
		return area;
	}

	public Area buscaPorCodigo(int codEmpresa, int codigo)
	{
		try
		{
			String sql ="SELECT a.id, a.nome, res.nome as responsavel FROM area as a, responsavel as res WHERE a.id=? and a.idempresa=? and a.idresponsavel=res.id";
			
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setInt(1, codigo);
			stmt.setInt(2, codEmpresa);
			
			Area area = new Area();	
			
			ResultSet rs = stmt.executeQuery();
			if(rs.next())
			{
				//Criando um nova instancia

				area.setCodigo(rs.getInt("id"));
				area.setNome(rs.getString("nome"));
				area.setResponsavel(rs.getString("responsavel"));
			
			}

			rs.close();
			stmt.close();
			return area;
			
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		
	}
	
	public Area getArea(Area area)
	{
		ResultSet rs;
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement("select nome from area where nome ILIKE ? and idempresa=?");
			stmt.setString(1, area.getNome());
			stmt.setInt(2, area.getCodEmpresa());
			
			rs=stmt.executeQuery();
			
			if(rs.next()) // Se area existir no banco, seta true
			{
				area.setAlerta(true);
			}
			
			else
			{
				area.setAlerta(false);
			}
			stmt.close();
			rs.close();
			
		}
		
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		return area;
		
	}
	
	public Area getAreaUp(Area area)
	{
		ResultSet rs;
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement("select nome from area where nome ILIKE ? and idempresa=? and id !=?");
			stmt.setString(1, area.getNome());
			stmt.setInt(2, area.getCodEmpresa());
			stmt.setInt(3, area.getCodigo());
			
			rs=stmt.executeQuery();
			
			if(rs.next()) //Se o nome da area digitada ja estiver cadastrada com outro id
			{
				area.setAlerta(true);
			}
			
			else
			{
				area.setAlerta(false);
			}
			stmt.close();
			rs.close();
		
		}
		
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		return area;
		
	}
}
